#include <iostream>
#include <pqxx/pqxx> 
#include <fstream>
#include <sstream>
using namespace std;
using namespace pqxx;

int main(int argc, char* argv[])
{
   //int year;
   //cout << "Enter a year to process export data:   ";
   //cin >> year;

   ofstream ndvi, lswi, evi;
   ndvi.open("ndvi.txt");
   lswi.open("lswi.txt");
   evi.open("evi.txt");

   int left_coo = 2232; int right_coo = 2394;
   int top_coo = 2225;  int bot_coo = 2334;

   string sql;
   stringstream sqlstream;
   
   try{
      connection C("dbname=mymodisdb user=demo1usr password=demo1pwd hostaddr=127.0.0.1 port=5432");
      if (C.is_open()) {
         cout << "Opened database successfully: " << C.dbname() << endl;
      } else {
         cout << "Can't open database" << endl;
         return 1;
      }

      //int total_cloud; int max_cloud = 0;
      for ( int x = left_coo ; x <= right_coo ; x++ )
      {
         for (int y = top_coo ; y <= bot_coo ; y++ )
         {
            ndvi << x << " " << y << "\t";
            lswi << x << " " << y << "\t";
            evi << x << " " << y << "\t";
            sqlstream.str("");//clear stream
            sqlstream << "\
            SELECT pixels.px_x, pixels.px_y, pixels.date_tkn, pixels.ndvi, pixels.lswi, pixels.evi, pixels.sur_refl_b03 \
            FROM public.pixels \
            WHERE px_x = "<< x <<" and px_y = "<< y <<" \
            ORDER BY pixels.date_tkn \
            LIMIT 184";// OFFSET "<< 46*(year-2009);
            //LIMIT 46 OFFSET "<< 46*(year-2009);
            sql = sqlstream.str();

            // query
            nontransaction N(C);      
            result R( N.exec( sql ));
            //total_cloud = 0;
            for (result::const_iterator c = R.begin(); c != R.end(); ++c)
            {
               // threshold to masked a pixel to be cloud and exclude in further analyze, according to Xiao's paper
               if ( c[6].as<double>() < 2000 ){
                  ndvi << c[3].as<double>() << "\t";
                  lswi << c[4].as<double>() << "\t";
                  evi << c[5].as<double>() << "\t";
               } else {
                  ndvi << 0 << "\t";
                  lswi << 0 << "\t";
                  evi << 0 << "\t";
                  //total_cloud++;
               }
            }
            //if ( total_cloud > max_cloud ) max_cloud = total_cloud;
            ndvi << endl;
            lswi << endl;
            evi << endl;
         }
         cout << "\t" << x << "/2394"<< endl;
      }
      cout << "Operation done successfully!" << endl;
      //cout << "Max cloud a year for a pixel:\t" << max_cloud << endl;
      C.disconnect ();
   }catch (const std::exception &e){
      cerr << e.what() << std::endl;
      return 1;
   }

   ndvi.close();
   lswi.close();
   evi.close();
   //cout << "Processed year=\t" << year << endl;
   cout << "Data is saved in ndvi.txt, lswi.txt, evi.txt" << endl << endl;
   return 0;
}